﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;

namespace Utilities
{
    public class ExcelReader
    {
        // "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<FILENAME>;Extended Properties=Excel 12.0;";
        private const string CONNECTION_STRING = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<FILENAME>;Extended Properties=Excel 12.0;";

        /// <summary>
        /// Lấy thông tin các Sheet trong file Excels
        /// </summary>
        /// <param name="fullFileName">Tên tập tin</param>
        /// <returns>Danh sách các SheetNames</returns>
        public static List<string> GetSheetNameFromExcelFile(string fullFileName)
        {
            List<string> SheetNames = new List<string>();
            OleDbConnection objConnection = new OleDbConnection();
            objConnection = new OleDbConnection(CONNECTION_STRING.Replace("<FILENAME>", fullFileName));
            DataSet dsImport = new DataSet();
            try
            {
                objConnection.Open();
                DataTable dtSchema = objConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                for (int i = 0; i < dtSchema.Rows.Count; i++)
                    SheetNames.Add(dtSchema.Rows[i].ItemArray[2].ToString());
            }
            catch (Exception)
            {
                //raise exception if needed
            }
            finally
            {
                // Clean up.
                if (objConnection != null)
                {
                    objConnection.Close();
                    objConnection.Dispose();
                }
            }


            return SheetNames;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fullFileName"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static DataTable GetDataTableFromExcelFile(string fullFileName, string sheetName)
        {
            OleDbConnection objConnection = new OleDbConnection();
            objConnection = new OleDbConnection(CONNECTION_STRING.Replace("<FILENAME>", fullFileName));
            DataSet dsImport = new DataSet();
            try
            {
                objConnection.Open();
                new OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", objConnection).Fill(dsImport);
            }
            catch (Exception)
            {
                //raise exception if needed
            }
            finally
            {
                // Clean up.
                if (objConnection != null)
                {
                    objConnection.Close();
                    objConnection.Dispose();
                }
            }
            return dsImport.Tables[0];
        }

        /// <summary>
        /// This method checks if the user entered sheetName exists in the Schema Table
        /// </summary>
        /// <param name="sheetName">Sheet name to be verified</param>
        /// <param name="dtSchema">schema table </param>
        private static bool CheckIfSheetNameExists(string sheetName, DataTable dtSchema)
        {
            foreach (DataRow dataRow in dtSchema.Rows)
            {
                if (sheetName == dataRow["TABLE_NAME"].ToString())
                {
                    return true;
                }
            }
            return false;
        }


    }
}
